<?php

    $rootDir = $_SERVER['DOCUMENT_ROOT'];

    require_once($rootDir.'/classes/LeproUser.class.php');
    require_once($rootDir.'/classes/mutex/SQLiteWMutex.class.php');
    
    if(!function_exists("bcmul")) {
        function bcmul($_ro, $_lo, $_scale=0)
        {
            return round($_ro*$_lo, $_scale);
        }
    }
    
    if(!function_exists("bcdiv")) {
        function bcdiv($_ro, $_lo, $_scale=0)
        {
            return round($_ro/$_lo, $_scale);
        }
    }

    class LeproUserDAO {
    
        public function __construct($db) {
            $this->_db = $db;
            // $this->deleteTable(); // TODO: remove
            $this->createTableIfNotExists();
        }
    
        public function getAll($packSize=-1, $offset=-1, $orderBy=null, $orderAsc=true) {
            $res = $this->_db->query('SELECT lid, username, cardid, gscore, groups, rtime FROM lusers'.
                (($orderBy != null) ? ' ORDER BY '.$orderBy : '').
                (($orderAsc && ($orderBy != null)) ? '' : ' DESC').
                (($packSize > -1) ? ' LIMIT '.$packSize : '').
                (($offset > -1) ? ' OFFSET '.$offset : ''));
            if ($res === false) return null;
            $usersArr = array();
            foreach ($res->fetchAll() as $userrow) {
                // $usersArr[] = new LeproUser($userrow['lid'], $userrow['username']);
                $user = new LeproUser($userrow['lid'], $userrow['username']);
                $user->setCardId($userrow['cardid']);
                $user->setGScore(bcdiv($userrow['gscore'],100,2));
                $user->assignGroups((strlen($userrow['groups']) > 0) ? explode(",",$userrow['groups']) : array());
                $user->setR10nTime($userrow['rtime']);
                $usersArr[$userrow['lid']] = $user;
            } 
            return $usersArr;
        }
        
        /* public function getAllIds($doOrder=true) {
            $res = $this->_db->query('SELECT lid FROM lusers'.($doOrder ? ' ORDER BY 1' : ''));
            if ($res === false) return null;
            $usersIdsArr = array();
            foreach ($res->fetchAll() as $userrow) {
                $usersIdsArr[] = $userrow['lid'];
            } 
            return $usersIdsArr;
        }
        
        public function getAllNames() {
            $res = $this->_db->query('SELECT username FROM lusers'.($doOrder ? ' ORDER BY lid' : ''));
            if ($res === false) return null;
            $usersNamesArr = array();
            foreach ($res->fetchAll() as $userrow) {
                $usersNamesArr[] = $userrow['username'];
            } 
            return $usersNamesArr;            
        } */
        
        public function getIdsNamesPairs($doOrder=true, $idFilterFunc=null) {
            $res = $this->_db->query('SELECT lid, username FROM lusers'.($doOrder ? ' ORDER BY lid' : ''));
            if ($res === false) return null;
            $usersIdsArr = array();
            foreach ($res->fetchAll() as $userrow) {
                $userId = $userrow['lid'];
                if ($idFilterFunc != null) $userId = call_user_func($idFilterFunc, $userrow['lid']);
                $usersIdsArr[$userId] = $userrow['username'];
            } 
            return $usersIdsArr;
        }        
        
        public function save($user) {
            $checkIfExistsQuery = 'SELECT lid FROM lusers WHERE lid = '.$user->getID();
            $res = $this->_db->query($checkIfExistsQuery);
            if ($res === false) return null;
            if ($res->numRows() == 0) {
                $query = 'INSERT INTO lusers (lid, username, cardid, pwdhash, email, rtime, gcount, gscore, groups) VALUES ('.$user->getID().', '.
                                      '\''.$user->getUsername().'\', '.
                                      '\''.$user->getCardID().'\', '.
                                      '\''.$user->getPwdHash().'\', '.
                                      '\''.$user->getEmail().'\', '.
                                      '\''.$user->getR10nTime().'\', '.
                                      '\''.$user->getGroupsCount().'\', '.
                                      '\''.bcmul($user->getGScore(),100,0).'\', '.                                      
                                      '\''.implode(",",$user->getGroupsIDs()).'\''.
                                      ' )';
            } else {
                $query = 'UPDATE lusers SET '.
                                'username=\''.$user->getUsername().'\', '.
                                'cardid=\''.$user->getCardID().'\', '.
                                'pwdhash=\''.$user->getPwdHash().'\', '.
                                'email=\''.$user->getEmail().'\', '.
                                'rtime=\''.$user->getR10nTime().'\', '.
                                'gcount=\''.$user->getGroupsCount().'\', '.
                                'gscore=\''.bcmul($user->getGScore(),100,0).'\', '.
                                'groups=\''.implode(",",$user->getGroupsIDs()).'\''.                                      
                                ' WHERE lid = '.$user->getID();
            }
            // $sqliteWMutex = SQLiteWMutex::getInstance();
            SQLiteWMutex::acquireWriteAccess();
            $res = $this->_db->queryExec($query);
            SQLiteWMutex::releaseWriteAccess();
            return $res;          
        }
        
        public function load($userId) {
            $res = $this->_db->query("SELECT lid, username, cardid, groups, pwdhash, email, gscore, rtime FROM lusers WHERE lid = ".$userId);
            if ($res === false) return null;
            $userMap = $res->fetchObject();
            if ($userMap->lid == null) return null; 
            $newUser = new LeproUser($userMap->lid, $userMap->username);
            $newUser->setCardId($userMap->cardid);
            $newUser->assignGroups((strlen($userMap->groups) > 0) ? explode(",",$userMap->groups) : array());
            $newUser->setPwdHash($userMap->pwdhash);
            $newUser->setEmail($userMap->email);
            $newUser->setGScore(bcdiv($userMap->gscore,100,2));
            $newUser->setR10nTime($userMap->rtime);
            return $newUser;         
        }
        
        public function delete($userId) {
            SQLiteWMutex::acquireWriteAccess();
            $this->_db->queryExec("DELETE FROM lusers WHERE lid = ".$userId);
            SQLiteWMutex::releaseWriteAccess();
        }
        
        public function getCount() {
            return $this->_db->query('SELECT COUNT(*) FROM lusers;')->fetchSingle();
        }
        
        protected function createTableIfNotExists() {
            //$this->_db->queryExec('CREATE TABLE IF NOT EXISTS lusers (id int, lid int, username varchar(50), PRIMARY KEY (lid));');
            $tablesCount = $this->_db->query('SELECT COUNT(*) FROM sqlite_master WHERE name=\'lusers\';')->fetchSingle();
            if ($tablesCount == 0) {
                //$sqliteWMutex = SQLiteWMutex::getInstance();
                SQLiteWMutex::acquireWriteAccess();
                $this->_db->queryExec('CREATE TABLE lusers (lid int, '.
                                                           'username varchar(50), '.
                                                           'cardid char(2), '.
                                                           'groups varchar(5120), '.
                                                           'pwdhash char(32), '.
                                                           'email varchar(120), '.
                                                           'gscore int, '.
                                                           'gcount int, '.
                                                           'rtime int, '.
                                                           'PRIMARY KEY (lid));');
                SQLiteWMutex::releaseWriteAccess();
            }
        }
        
        protected function deleteTable() {
            SQLiteWMutex::acquireWriteAccess();
            $this->_db->queryExec('DROP TABLE lusers;');
            $this->_db->queryExec('DROP INDEX gscrlus;');            
            SQLiteWMutex::releaseWriteAccess();
        }
        
        public function deleteAllUsers() {
            $tablesCount = $this->_db->query('SELECT COUNT(*) FROM sqlite_master WHERE name=\'lusers\';')->fetchSingle();
            if ($tablesCount > 0) {        
                $this->deleteTable();
                $this->createTableIfNotExists();
            }
        } 
        
        public function getUsersCards($usersIdsList) {
            if (count($usersIdsList) > 0) {                
                $res = $this->_db->singleQuery('SELECT cardid FROM lusers WHERE lid IN ('.implode(',',$usersIdsList).')');
                if ($res === false) return null;
                return (is_string($res) ? array($res) : $res);
            } else return array();
        } 
        
        public function getUsersAndCards($usersIdsList, $idFilterFunc=null) {
            if (count($usersIdsList) > 0) {           
                $res = $this->_db->query('SELECT lid, cardid FROM lusers WHERE lid IN ('.implode(',',$usersIdsList).')');     
                if ($res === false) return null;
                $usersIdsArr = array();
                foreach ($res->fetchAll() as $userrow) {
                    $userId = $userrow['lid'];
                    if ($idFilterFunc != null) $userId = call_user_func($idFilterFunc, $userrow['lid']);
                    $usersIdsArr[$userId] = $userrow['cardid'];
                } 
                return $usersIdsArr;
            } else return array();
        }        
        
        public function getUsersNames($usersIdsList) {
            if (count($usersIdsList) > 0) {                
                $res = $this->_db->singleQuery('SELECT username FROM lusers WHERE lid IN ('.implode(',',$usersIdsList).')');
                if ($res === false) return null;
                return (is_string($res) ? array($res) : $res);
            } else return array();
        } 
        
        public function getUserId($userName) {
            return $this->_db->query('SELECT lid FROM lusers WHERE username=\''.$userName.'\';')->fetchSingle();
        }
        
        public function getUserPos($userId) {
            $indexesExists = $this->_db->query('SELECT COUNT(*) FROM sqlite_master WHERE name=\'gscrlus\';')->fetchSingle();
            if ($indexesExists == 0) $this->_db->queryExec('CREATE INDEX gscrlus on lusers(gscore DESC);');
            $res = $this->_db->query('SELECT lid FROM lusers ORDER BY gscore DESC;')->fetchAll(SQLITE_NUM);
            if ($res === false) return null;
            return array_search(array($userId), $res) + 1;
        }
        
    }

?>
